Software applications such as spreadsheet and database programs use mathematical formulas to compute values from stored data. Normally, they require users to create and view such formulas as text, that is, as one-dimensional mathematical expressions. As these expressions grow longer and more complicated, it becomes increasingly difficult for users to read them and to understand their algebraic or logical structure.
Consider, for example, the following spreadsheet formula, which is intended to represent the definition of the normal probability distribution function shown in FIG. 1.=1/SQRT(2*PI( ))*EXP(−A3^2/2)
When used with certain spreadsheet programs, such as Microsoft® Excel® (“Excel”), this formula causes a subtle error, which is difficult for users to perceive from its display as text, but which the present invention clearly exposes (see the discussion below of elements 541 and 552 in FIG. 5).
Consider, for another example, the spreadsheet formula=IF(OR(‘Sheet2’!E1=“Final”,‘Sheet2’!E1=“Draft”,‘Sheet2’!E1=“None”),IF(‘Sheet2’!E1=“Final”,IF(OR(‘Sheet2’!M1>‘Sheet’!F1,AND(‘Sheet2’!M1=‘Sheet2’!F1,‘Sheet2’!N1>=‘Sh eet2’!G1True,False),‘Sheet2’!E1),“ ”)
This formula contains nested logical functions that test values in referenced cells in a spreadsheet, and its value depends on the values in the referenced cells and on a decision tree represented by the nested logical functions. But the structure of that decision tree is difficult for users to perceive from its display as text.
The process of debugging such formulas to ensure they produce the intended results is time consuming and fraught with errors. It is easy to overlook or miscount parentheses, which control the order of evaluation for a formula. Some programs color code matching parentheses to help identify the constituent phrases in a formula, but this coding is difficult to see and comprehend. Users may add spaces and line breaks to help clarify the structure implicit in a textual display, but such formatting provides no convenient means for navigation among a formula's constituent phrases. This invention not only presents an organized view of the formula but also provides navigation controls, interim values, and other aids that help users understand and debug their formulas.